package rsj.dao;

import com.rlyy.aibase.db.QueryRunner;
import com.rlyy.aibase.utils.base.MapNode;
import com.rlyy.aibase.utils.base.Node;
import com.rlyy.aibase.utils.base.TableNode;
import com.rlyy.aibase.utils.helpers.DateHelper;
import common.util.Assert;
import common.util.PasswordUtil;
import org.apache.commons.lang3.StringUtils;
import rsj.api.ApiVariable;
import rsj.table.SYS_ORG;
import rsj.table.SYS_ORGP;
import rsj.table.User;
import rsj.table.UserType;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;

public class OrgDao extends BaseDao{
	private static final OrgDao instance = new OrgDao();

	public static OrgDao getInstance(){
		return instance;
	}

	private static UserInfoDao infoDao = UserInfoDao.getInstance();

	public MapNode findOrgList(Connection conn,int size,int pcount,String querykey) throws SQLException{
		QueryRunner queryRunner = new QueryRunner();
    	MapNode responsedata = new MapNode();
    	StringBuffer countsql = new StringBuffer();
    	countsql.append("select count(1) from "+SYS_ORG.Table_Name);
    	Node node=null;
    	if(StringUtils.isNoneBlank(querykey)){
    		countsql.append(" where "+SYS_ORG.Org_Sname).append(" like ?");
    		node = queryRunner.queryLinkNode(conn, countsql.toString(),"%"+querykey+"%");
    	}else{
    		node = queryRunner.queryLinkNode(conn, countsql.toString());
    	}

    	int count = node.valueInt();
    	responsedata.putNode("Org_Count", String.valueOf(count));
    	if(count == 0){
    		responsedata.putNode("Org_List", "");
    		return responsedata;
    	}

		StringBuffer sql = new StringBuffer();
		sql.append("select o."+SYS_ORG.Org_Id+" , min(p."+SYS_ORGP.Orgp_Status+") orgp_status ,")
			.append("min(p."+SYS_ORGP.Orgp_Ctime+") orgp_ctime,o."+SYS_ORG.Org_Manbus+",o."+SYS_ORG.Org_Sname)
			.append(" from "+SYS_ORG.Table_Name+" o left join " +
					"(SELECT * FROM sys.sys_orgp orgp WHERE orgp.orgp_orgpstatus = '1') p on o."+SYS_ORG.Org_Id)
			.append(" = p."+SYS_ORGP.Orgp_Org_Id);
		if(StringUtils.isNoneBlank(querykey)){
			sql.append(" where "+SYS_ORG.Org_Sname).append(" like ?");
		}

			sql.append(" group by o."+SYS_ORG.Org_Id+",o."+SYS_ORG.Org_Sname+",o."+SYS_ORG.Org_Manbus)
				.append(" order by "+SYS_ORG.Org_Utime+" desc limit ? Offset ?");
		if(StringUtils.isNoneBlank(querykey)){
			TableNode map = queryRunner.queryLinkTableNode(conn,sql.toString(),"%"+querykey+"%",size,size*(pcount-1));
			responsedata.putNode("Org_List", map.toJson());
		}else{
			TableNode map = queryRunner.queryLinkTableNode(conn,sql.toString(),size,size*(pcount-1));
			responsedata.putNode("Org_List", map.toJson());
		}


		return responsedata;
	}

	/**
	 * 增加机构，并增加用户
	 */

	public void orgInsert(Connection conn,String id,String orgName,String orgsname,String orgCreditcode,String legname,String addr,
							String manopr,String manoprphone,String manoprmail,String mantech,String mantechphone,String manterchmail,
							String manbus,String manbusphone,String manbusmail,String orgip,String uname,String password)throws SQLException{

		QueryRunner queryRunner = new QueryRunner();

		StringBuffer findsql = new StringBuffer();
		findsql.append("select count(1) from "+User.Table_Name+" where "+User.User_Uname+" = ?");
	    Node node = queryRunner.queryLinkNode(conn, findsql.toString(),uname);
	    int count = node.valueInt();

	     Assert.isTrue(count == 0, "客户Id已经存在，不能添加！");

		StringBuffer insertsql = new StringBuffer();
		insertsql.append("insert into "+SYS_ORG.Table_Name +"("+SYS_ORG.Org_Id+","+SYS_ORG.Org_Name+","+SYS_ORG.Org_Sname+",")
				 .append(SYS_ORG.Org_Creditcode+","+SYS_ORG.Org_Legname+","+SYS_ORG.Org_Addr+","+SYS_ORG.Org_Manoper+","+SYS_ORG.Org_Manoperphone)
				 .append(","+SYS_ORG.Org_Manopermail+","+SYS_ORG.Org_Mantech+","+SYS_ORG.Org_Mantechphone+","+SYS_ORG.Org_Mantechmail)
				 .append(","+SYS_ORG.Org_Manbus+","+SYS_ORG.Org_Manbusphone+","+SYS_ORG.Org_Manbusmail+","+SYS_ORG.Org_Ip+","+SYS_ORG.Org_User_Name+","+SYS_ORG.Org_Utime)
				 .append(") values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		queryRunner.update(conn,insertsql.toString(),id,orgName,orgsname,orgCreditcode,legname,addr,manopr,manoprphone,manoprmail,mantech,mantechphone,manterchmail,
							manbus,manbusphone,manbusmail,orgip,uname,DateHelper.strToTimestamp(DateHelper.getDate(new Date())));

		infoDao.insertUser(conn, id, uname, orgsname, id, "", UserType.user_type_org, PasswordUtil.stringToMD5(password), "", "", ApiVariable.orgRoleId);
	}

	public void orgUpdate(Connection con,String id,String orgName,String orgsname,String orgCreditcode,String legname,String addr,
							String manopr,String manoprphone,String manoprmail,String mantech,String mantechphone,String manterchmail,
							String manbus,String manbusphone,String manbusmail,String orgip)throws SQLException{

		QueryRunner queryRunner = new QueryRunner();
		StringBuffer updatesql = new StringBuffer();
		updatesql.append("update "+SYS_ORG.Table_Name+" set "+SYS_ORG.Org_Name+" = ?")
				 .append(","+SYS_ORG.Org_Sname+" = ?")
				 .append(","+SYS_ORG.Org_Creditcode+" = ?")
				 .append(","+SYS_ORG.Org_Legname+" = ?")
				 .append(","+SYS_ORG.Org_Addr+" = ?")
				 .append(","+SYS_ORG.Org_Manoper+" = ?")
				 .append(","+SYS_ORG.Org_Manoperphone+" = ?")
				 .append(","+SYS_ORG.Org_Manopermail+" = ?")
				 .append(","+SYS_ORG.Org_Mantech+" = ?")
				 .append(","+SYS_ORG.Org_Mantechphone+" = ?")
				 .append(","+SYS_ORG.Org_Mantechmail+" = ?")
				 .append(","+SYS_ORG.Org_Manbus+" = ?")
				 .append(","+SYS_ORG.Org_Manbusphone+" = ?")
				 .append(","+SYS_ORG.Org_Manbusmail+" = ?")
				 .append(","+SYS_ORG.Org_Ip+" = ?")
				 .append(","+SYS_ORG.Org_Utime+" = ?")
				 .append(" where "+SYS_ORG.Org_Id+"=?");

		queryRunner.update(con,updatesql.toString(),orgName,orgsname,orgCreditcode,legname,addr,manopr,manoprphone,manoprmail,mantech,mantechphone,manterchmail,
							manbus,manbusphone,manbusmail,orgip,DateHelper.strToTimestamp(DateHelper.getDate(new Date())),id);
	}


	public MapNode findOrgInfoById(Connection con,String orgId){

		QueryRunner queryRunner = new QueryRunner();
		StringBuffer findsql = new StringBuffer();
		findsql.append("select "+SYS_ORG.Org_Id+","+SYS_ORG.Org_Name+","+SYS_ORG.Org_Sname+",")
				 .append(SYS_ORG.Org_Creditcode+","+SYS_ORG.Org_Legname+","+SYS_ORG.Org_Addr+","+SYS_ORG.Org_Manoper+","+SYS_ORG.Org_Manoperphone)
				 .append(","+SYS_ORG.Org_Manopermail+","+SYS_ORG.Org_Mantech+","+SYS_ORG.Org_Mantechphone+","+SYS_ORG.Org_Mantechmail)
				 .append(","+SYS_ORG.Org_Manbus+","+SYS_ORG.Org_Manbusphone+","+SYS_ORG.Org_Manbusmail+","+SYS_ORG.Org_Ip+","+SYS_ORG.Org_Id+","+SYS_ORG.Org_User_Name);
		findsql.append(" from "+SYS_ORG.Table_Name +" where "+SYS_ORG.Org_Id+" = ?");

		MapNode map = queryRunner.queryLinkRow(con, findsql.toString(), orgId);
		return map;
	}

	public MapNode findOrgById(String orgId){
		return (MapNode) invokeSql(conn -> {
			return findOrgInfoById(conn, orgId);
		});
	}

	public MapNode findUserOrgInfo(Connection con,String userId){
		QueryRunner queryRunner = new QueryRunner();
		StringBuffer findsql = new StringBuffer();

		findsql.append(" select u.user_id,u.user_uname,c.cust_name as org_name,c.cust_manoper as org_manoper,c.cust_manoperphone as org_manoperphone,c.cust_manopermail as org_manopermail")
				.append(" from sys.sys_user u ")
				.append(" left join sys.sys_org o on u.user_org_id = o.org_id ")
				.append(" left join sys.sys_cust c on o.org_cust_id = c.cust_id ")
				.append(" where u."+User.User_Id+" = ? ");
		MapNode map = queryRunner.queryLinkRow(con, findsql.toString(), userId);
		return map;
	}


	public TableNode getOrgIdsAll() {
		return (TableNode) invokeSql(conn -> {
			String sql = String.format(selectWithoutWhere, " org_id ", SYS_ORG.Table_Name);
			return getQueryRunner().queryLinkTableNode(conn, sql);
		});
	}
}
